package org.test.jiazi_1.service.impl;

import cn.idev.excel.FastExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.test.jiazi_1.common.exception.BusinessException;
import org.test.jiazi_1.common.utils.BeanCopyUtils;
import org.test.jiazi_1.common.utils.IdCardAddressResolver;
import org.test.jiazi_1.config.Constant;
import org.test.jiazi_1.pojo.dto.StudentCourseInfoExportDTO;
import org.test.jiazi_1.pojo.dto.StudentCourseInfoImportDTO;
import org.test.jiazi_1.pojo.entity.Course;
import org.test.jiazi_1.pojo.entity.Fitness;
import org.test.jiazi_1.pojo.entity.Scores;
import org.test.jiazi_1.pojo.entity.Student;
import org.test.jiazi_1.pojo.entity.Teacher;
import org.test.jiazi_1.service.CourseService;
import org.test.jiazi_1.service.FitnessService;
import org.test.jiazi_1.service.ImportOrExportService;
import org.test.jiazi_1.service.ScoresService;
import org.test.jiazi_1.service.StudentService;
import org.test.jiazi_1.service.TeacherService;

import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;

import static org.test.jiazi_1.common.utils.IdCardAddressResolver.getProvinceCityAddress;

/**
 * 导入导出服务实现
 * - 导入：读取 Excel -> 校验 -> 批量创建教师/课程/学生/体测(Fitness)/评分(Scores)
 * - 导出：按学期查询 Course -> 查询 Fitness -> 组装并导出 StudentCourseInfoExporttDTO
 */
@Slf4j
@Service("studentCourseInfoService")
public class ImportOrExportServiceImpl implements ImportOrExportService {

    @Resource
    private StudentService studentService;

    @Resource
    private TeacherService teacherService;

    @Resource
    private CourseService courseService;

    @Resource
    private FitnessService fitnessService;

    @Resource
    private ScoresService scoresService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void importExcel(InputStream in) {
        // 1. 读取 Excel
        List<StudentCourseInfoImportDTO> rows = readExcelData(in);
        if (rows.isEmpty()) {
            log.warn("导入文件为空或未读取到数据");
            return;
        }

        // 2. 校验并收集错误（逐行）
        ValidationResult vr = validateRows(rows);
        if (vr.hasErrors()) {
            String summary = buildErrorSummary(vr);
            log.warn("导入校验未通过：{}", summary);
            throw new BusinessException(400, summary);
        }

        // 3. 执行批量处理：教师 -> 课程 -> 学生 -> Fitness -> Scores
        processImportInBatches(rows);

        log.info("导入完成，共 {} 条记录", rows.size());
    }

    // 读取 Excel 数据为 DTO 列表
    private List<StudentCourseInfoImportDTO> readExcelData(InputStream in) {
        try {
            List<StudentCourseInfoImportDTO> list = FastExcel.read(in)
                    .sheet(0)
                    .head(StudentCourseInfoImportDTO.class)
                    .doReadSync();
            return list == null ? Collections.emptyList() : list;
        } catch (Exception e) {
            log.error("读取导入文件失败", e);
            throw new BusinessException(500, "读取导入文件失败: " + e.getMessage());
        }
    }

    // 校验逻辑（示例规则，可按需扩展）
    private ValidationResult validateRows(List<StudentCourseInfoImportDTO> rows) {
        ValidationResult vr = new ValidationResult();

        validateDuplicateStudentNos(rows, vr);
        validateRequiredFields(rows, vr);

        return vr;
    }

    private void validateDuplicateStudentNos(List<StudentCourseInfoImportDTO> rows, ValidationResult vr) {
        rows.stream()
                .map(r -> trimToNull(r.getStudentNo()))
                .filter(Objects::nonNull)
                .collect(Collectors.groupingBy(Function.identity(), Collectors.counting()))
                .entrySet().stream()
                .filter(entry -> entry.getValue() > 1)
                .forEach(entry -> vr.addError("导入文件中存在重复学籍号: " + entry.getKey()));
    }

    private void validateRequiredFields(List<StudentCourseInfoImportDTO> rows, ValidationResult vr) {
        for (int i = 0; i < rows.size(); i++) {
            validateSingleRow(rows.get(i), i + 1, vr);
        }
    }

    private void validateSingleRow(StudentCourseInfoImportDTO row, int rowNumber, ValidationResult vr) {
        String location = getRowLocation(row, rowNumber);

        validateField(row.getStudentName(), "学生姓名", location, vr);
        validateField(row.getStudentNo(), "学籍号", location, vr);
        validateField(row.getTerm(), "学期信息", location, vr);
        validateField(row.getCourseNo(), "课号信息", location, vr);
        validateIdCard(row.getIdCard(), location, vr);
    }

    private String getRowLocation(StudentCourseInfoImportDTO row, int rowNumber) {
        return row.getStudentNo() != null ?
                "(学籍号=" + row.getStudentNo() + ")" :
                "(行=" + rowNumber + ")";
    }

    private void validateField(String value, String fieldName, String location, ValidationResult vr) {
        if (isBlank(value)) {
            vr.addError("缺少" + fieldName + " " + location);
        }
    }

    private void validateIdCard(String idCard, String location, ValidationResult vr) {
        if (isNotBlank(idCard)) {
            String id = idCard.trim();
            if (id.length() != 15 && id.length() != 18) {
                vr.addError("身份证号长度不正确 " + location + ": " + id);
            }
        }
    }


    private String buildErrorSummary(ValidationResult vr) {
        return String.format("导入失败，校验未通过，总计 %d 项错误：%n%s", vr.getErrorCount(), String.join(System.lineSeparator(),
                vr.getErrors()));
    }

    private static class ValidationResult {
        private final List<String> errors = new ArrayList<>();

        void addError(String e) {
            errors.add(e);
        }

        boolean hasErrors() {
            return !errors.isEmpty();
        }

        int getErrorCount() {
            return errors.size();
        }

        List<String> getErrors() {
            return Collections.unmodifiableList(errors);
        }
    }

    // 处理批量导入（按顺序创建关联数据）
    private void processImportInBatches(List<StudentCourseInfoImportDTO> rows) {
        // 1) 教师
        Map<String, Integer> teacherMap = ensureTeachers(rows);
        // 2) 课程
        Map<String, Integer> courseMap = ensureCourses(rows, teacherMap);
        // 3) 学生 + 体测 + 评分
        ensureStudentsAndFitness(rows, courseMap);
    }

    // 批量确保教师存在，返回 姓名->id 映射
    private Map<String, Integer> ensureTeachers(List<StudentCourseInfoImportDTO> rows) {
        Set<String> names =
                rows.stream().map(r -> trimToNull(r.getTeacherName())).filter(Objects::nonNull).collect(Collectors.toSet());
        if (names.isEmpty()) return Collections.emptyMap();
        QueryWrapper<Teacher> qw = new QueryWrapper<>();
        qw.in("teacher_name", names);
        List<Teacher> exist = teacherService.list(qw);
        Map<String, Integer> map = exist.stream().collect(Collectors.toMap(Teacher::getTeacherName, Teacher::getId));
        List<Teacher> toCreate = names.stream().filter(n -> !map.containsKey(n)).map(n -> {
            Teacher t = new Teacher();
            t.setTeacherName(n);
            t.setCreateTime(new Date());
            return t;
        }).collect(Collectors.toList());
        if (!toCreate.isEmpty()) {
            teacherService.saveBatch(toCreate);
            toCreate.forEach(t -> map.put(t.getTeacherName(), t.getId()));
        }
        return map;
    }

    // 批量确保课程存在（以 term_courseId 为 key），返回 key->courseId
    private Map<String, Integer> ensureCourses(List<StudentCourseInfoImportDTO> rows, Map<String, Integer> teacherMap) {
        Map<String, Integer> courseKeyToId = new HashMap<>();
        for (StudentCourseInfoImportDTO r : rows) {
            String term = trimToNull(r.getTerm());
            String courseNo = trimToNull(r.getCourseNo());
            if (term == null || courseNo == null) continue;
            String key = term + "_" + courseNo;
            if (courseKeyToId.containsKey(key)) continue;
            QueryWrapper<Course> qw = new QueryWrapper<>();
            qw.eq("term", term).eq("course_no", courseNo);
            List<Course> cs = courseService.list(qw);
            if (cs != null && !cs.isEmpty()) {
                courseKeyToId.put(key, cs.get(0).getId());
                continue;
            }
            Course c = new Course();
            c.setTerm(term);
            c.setCourseNo(courseNo);
            c.setCourseName(trimToNull(r.getCourseName()) != null ? trimToNull(r.getCourseName()) : "课程_" + courseNo);
            c.setCreateTime(new Date());
            c.setGradeId(parseInteger(r.getGradeId()));
            Integer tid = teacherMap.get(trimToNull(r.getTeacherName()));
            c.setTeacherId(tid);
            courseService.save(c);
            courseKeyToId.put(key, c.getId());
        }
        return courseKeyToId;
    }

    // 确保学生存在并为新学生创建 Fitness 与 Scores
    // 确保学生存在并为新学生创建 Fitness 与 Scores
    private void ensureStudentsAndFitness(List<StudentCourseInfoImportDTO> rows, Map<String, Integer> courseKeyToId) {
        Set<String> studentNos = rows.stream().map(r -> trimToNull(r.getStudentNo())).filter(Objects::nonNull).collect(Collectors.toSet());
        if (studentNos.isEmpty()) return;

        // 查询已有学生
        QueryWrapper<Student> sq = new QueryWrapper<>();
        sq.in("student_no", studentNos);
        List<Student> exist = studentService.list(sq);
        Map<String, Student> existMap = exist.stream().collect(Collectors.toMap(Student::getStudentNo, Function.identity()));

        List<Student> toCreate = new ArrayList<>();
        List<Student> toUpdate = new ArrayList<>();
        List<NewFitnessInfo> fitnessToCreate = new ArrayList<>();

        // 处理每一行数据
        for (StudentCourseInfoImportDTO row : rows) {
            String studentNo = trimToNull(row.getStudentNo());
            if (studentNo == null) continue;

            Student student = existMap.get(studentNo);

            if (student != null) {
                // 更新已有学生
                updateExistingStudent(row, student, toUpdate);
            } else {
                // 创建新学生
                student = createNewStudent(row);
                toCreate.add(student);
                existMap.put(studentNo, student); // 添加到map中，避免重复创建
            }

            // 为所有学生创建 Fitness 记录（如果需要）
            String term = trimToNull(row.getTerm());
            String courseId = trimToNull(row.getCourseNo());
            if (term != null && courseId != null) {
                fitnessToCreate.add(new NewFitnessInfo(studentNo, term, courseId));
            }
        }

        // 执行批量操作
        StudentBatchOperations batchOps = new StudentBatchOperations(toCreate, toUpdate, fitnessToCreate);
        executeStudentBatchOperations(batchOps);

        // 创建 Fitness 记录
        createFitnessAndScoresForNewStudents(fitnessToCreate, courseKeyToId);
    }

    private Student createNewStudent(StudentCourseInfoImportDTO row) {
        Student student = new Student();
        BeanCopyUtils.copy(row, student);
        // 设置年级信息（根据学号前两位 + "0000"）
        student.setStudentSource(getFirstTwoAndFourZeros(row.getIdCard()));
        student.setHomeAddress(getProvinceCityAddress(row.getIdCard()));
        student.setCreateTime(new Date());
        return student;
    }



    private void updateExistingStudent(StudentCourseInfoImportDTO row, Student student, List<Student> toUpdate) {
        boolean changed = updateStudentFields(student, row);
        if (changed) {
            toUpdate.add(student);
        }
    }

    private boolean updateStudentFields(Student student, StudentCourseInfoImportDTO row) {
        boolean changed = false;
        changed |= updateIfChanged(student::getStudentName, student::setStudentName, trimToNull(row.getStudentName()));
        changed |= updateIfChanged(student::getGender, student::setGender, row.getGender());
        changed |= updateIfChanged(student::getBirthDate, student::setBirthDate, trimToNull(row.getBirthDate()));
        changed |= updateIfChanged(student::getIdCard, student::setIdCard, trimToNull(row.getIdCard()));
        changed |= updateIfChanged(student::getNationCode, student::setNationCode, trimToNull(row.getNationCode()));
        changed |= updateIfChanged(student::getHomeAddress, student::setHomeAddress, trimToNull(row.getHomeAddress()));
        return changed;
    }



    private void executeStudentBatchOperations(StudentBatchOperations batchOps) {
        if (!batchOps.getToCreate().isEmpty()) {
            studentService.saveBatch(batchOps.getToCreate());
            log.info("批量创建学生 {} 条", batchOps.getToCreate().size());
        }

        if (!batchOps.getToUpdate().isEmpty()) {
            studentService.updateBatchById(batchOps.getToUpdate());
            log.info("批量更新学生 {} 条", batchOps.getToUpdate().size());
        }
    }

    private void createFitnessAndScoresForNewStudents(List<NewFitnessInfo> fitnessToCreate,
                                                      Map<String, Integer> courseKeyToId) {
        if (fitnessToCreate.isEmpty()) return;

        Map<String, Integer> studentNoToId = getStudentIds(fitnessToCreate);
        List<Fitness> fitnessList = buildFitnessRecords(fitnessToCreate, studentNoToId, courseKeyToId);

        if (!fitnessList.isEmpty()) {
            fitnessService.saveBatch(fitnessList);
            log.info("批量创建 Fitness {} 条", fitnessList.size());
            // createScoresForFitness(fitnessList); // 移除创建Scores的调用
        }
    }

    private Map<String, Integer> getStudentIds(List<NewFitnessInfo> fitnessToCreate) {
        Set<String> studentNos = fitnessToCreate.stream()
                .map(NewFitnessInfo::studentNo)
                .collect(Collectors.toSet());

        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("student_no", studentNos);
        List<Student> students = studentService.list(queryWrapper);

        return students.stream()
                .collect(Collectors.toMap(Student::getStudentNo, Student::getId));
    }

    private List<Fitness> buildFitnessRecords(List<NewFitnessInfo> fitnessToCreate,
                                              Map<String, Integer> studentNoToId,
                                              Map<String, Integer> courseKeyToId) {
        List<Fitness> fitnessList = new ArrayList<>();

        for (NewFitnessInfo info : fitnessToCreate) {
            Integer studentId = studentNoToId.get(info.studentNo());
            Integer courseId = courseKeyToId.get(info.term() + "_" + info.courseId());

            if (studentId != null && courseId != null) {
                Fitness fitness = new Fitness();
                fitness.setStudentId(studentId);
                fitness.setCourseId(courseId);
                fitness.setCreateTime(new Date());
                fitness.setFlag(1);
                fitnessList.add(fitness);
            }
        }

        return fitnessList;
    }


    // 辅助记录类
    private record NewFitnessInfo(String studentNo, String term, String courseId) {}

    // 批量操作容器类
    private static class StudentBatchOperations {
        private final List<Student> toCreate;
        private final List<Student> toUpdate;
        private final List<NewFitnessInfo> fitnessToCreate;

        public StudentBatchOperations(List<Student> toCreate, List<Student> toUpdate,
                                      List<NewFitnessInfo> fitnessToCreate) {
            this.toCreate = toCreate;
            this.toUpdate = toUpdate;
            this.fitnessToCreate = fitnessToCreate;
        }

        public List<Student> getToCreate() { return toCreate; }
        public List<Student> getToUpdate() { return toUpdate; }
        public List<NewFitnessInfo> getFitnessToCreate() { return fitnessToCreate; }
    }


    // ---------- 导出功能 ----------
    @Override
    public void exportExcel(String semester, HttpServletResponse response) {
        try {
            // 1) 查询该学期的课程
            QueryWrapper<Course> cq = new QueryWrapper<>();
            cq.eq("term", semester);
            List<Course> courses = courseService.list(cq);
            if (courses == null || courses.isEmpty()) {
                handleEmptyExport(response, semester);
                return;
            }

            // 2) 查询这些课程对应的 Fitness
            Set<Integer> courseIds = courses.stream().map(Course::getId).collect(Collectors.toSet());
            QueryWrapper<Fitness> fq = new QueryWrapper<>();
            fq.in("course_id", courseIds);
            List<Fitness> fitnessList = fitnessService.list(fq);
            if (fitnessList == null || fitnessList.isEmpty()) {
                handleEmptyExport(response, semester);
                return;
            }

            // 3) 批量加载关联数据并组装 DTO
            ExportContext ctx = buildExportContext(fitnessList, courses);
            List<StudentCourseInfoExportDTO> exportList = fitnessList.stream().map(f -> buildExportDtoFromFitness(f,
                    ctx)).filter(Objects::nonNull).collect(Collectors.toList());

            // 4) 导出
            String filename = URLEncoder.encode("学生课程信息_" + semester + ".xlsx", StandardCharsets.UTF_8).replaceAll(
                    "\\+", "%20");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + filename);
            FastExcel.write(response.getOutputStream()).sheet(0).head(StudentCourseInfoExportDTO.class).doWrite(exportList);

        } catch (Exception e) {
            log.error("导出失败", e);
            try {
                response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "导出失败");
            } catch (IOException ex) {
                log.error("响应发送失败", ex);
            }
        }
    }

    private void handleEmptyExport(HttpServletResponse response, String semester) throws IOException {
        response.setStatus(HttpServletResponse.SC_NO_CONTENT);
        response.getWriter().write("未找到指定学期的数据");
    }

    private static class ExportContext {
        final Map<Integer, Student> students;
        final Map<Integer, Teacher> teachers;
        final Map<Integer, Course> courses;

        ExportContext(Map<Integer, Student> s, Map<Integer, Teacher> t, Map<Integer, Course> c) {
            this.students = s;
            this.teachers = t;
            this.courses = c;
        }
    }

    private ExportContext buildExportContext(List<Fitness> fitnessList, List<Course> courses) {
        Set<Integer> studentIds =
                fitnessList.stream().map(Fitness::getStudentId).filter(Objects::nonNull).collect(Collectors.toSet());
        Set<Integer> teacherIds =
                courses.stream().map(Course::getTeacherId).filter(Objects::nonNull).collect(Collectors.toSet());
        Map<Integer, Student> sMap = studentIds.isEmpty() ? Collections.emptyMap() :
                studentService.listByIds(studentIds).stream().collect(Collectors.toMap(Student::getId,
                        Function.identity()));
        Map<Integer, Teacher> tMap = teacherIds.isEmpty() ? Collections.emptyMap() :
                teacherService.listByIds(teacherIds).stream().collect(Collectors.toMap(Teacher::getId,
                        Function.identity()));
        Map<Integer, Course> cMap = courses.stream().collect(Collectors.toMap(Course::getId, Function.identity()));
        return new ExportContext(sMap, tMap, cMap);
    }

    private StudentCourseInfoExportDTO buildExportDtoFromFitness(Fitness f, ExportContext ctx) {
        try {
            StudentCourseInfoExportDTO dto = new StudentCourseInfoExportDTO();
            if (f.getStudentId() != null && ctx.students.containsKey(f.getStudentId()))
                BeanCopyUtils.copy(ctx.students.get(f.getStudentId()), dto);
            BeanCopyUtils.copy(f, dto);
            Course c = ctx.courses.get(f.getCourseId());
            if (c != null) {
                dto.setTerm(c.getTerm());
                dto.setCourseNo(c.getCourseNo() == null ? null : c.getCourseNo());
                dto.setGradeId(c.getGradeId() == null ? null : String.valueOf(c.getGradeId()));
                if (c.getTeacherId() != null && ctx.teachers.containsKey(c.getTeacherId()))
                    dto.setTeacherName(ctx.teachers.get(c.getTeacherId()).getTeacherName());
            }
            return dto;
        } catch (Exception e) {
            log.warn("构建导出 DTO 失败, fitnessId={}", f.getId(), e);
            return null;
        }
    }

    // 辅助方法
    private Integer parseInteger(String v) {
        try {
            return v == null ? null : Integer.valueOf(v.trim());
        } catch (Exception e) {
            return null;
        }
    }

    private String trimToNull(String s) {
        return s == null ? null : s.trim().isEmpty() ? null : s.trim();
    }

    private boolean isBlank(String s) {
        return s == null || s.trim().isEmpty();
    }

    private boolean isNotBlank(String s) {
        return !isBlank(s);
    }

    private <T> boolean updateIfChanged(Supplier<T> getter, Consumer<T> setter, T newVal) {
        T old = getter.get();
        if (newVal != null && !newVal.equals(old)) {
            setter.accept(newVal);
            return true;
        }
        return false;
    }

    private static String getFirstTwoAndFourZeros(String s) {
        return (s != null && s.length() >= 2) ? s.substring(0, 2) + "0000" : null;
    }

}
